package com.cmpe.av.logic;

import java.sql.*;

import com.cmpe.av.model.Comment;
import com.cmpe.av.model.User;
import java.util.Date;


public class UserLogic {
	private Connection connect = null;
	private Statement statement = null;
	private ResultSet resultSet = null;
	private final String USERNAME = "project6";
	private final String PASSWORD = "17Souf";
	private final String URL = "jdbc:mysql://titan.cmpe.boun.edu.tr:3306/database6";

	public UserLogic() throws Exception{
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager
					.getConnection(URL,USERNAME,PASSWORD);  


		} catch (Exception e) {
			throw e;
		} 
	};

	public User getUser(long userid)
	{
		User user = new User();

		try
		{
			String query = "SELECT * FROM Users WHERE UserID =" + userid;
			statement = connect.createStatement();
			resultSet = statement.executeQuery(query);
			Date current = new Date();

			while(resultSet.next())
			{
				user.setAll(Integer.parseInt(resultSet.getString("UserID")),
						resultSet.getString("Name"),
						resultSet.getString("Surname"),
						resultSet.getString("Username"),
						current, Integer.parseInt(resultSet.getString("isAdmin")),
						resultSet.getString("email"), resultSet.getString("password"),
						Integer.parseInt(resultSet.getString("Score")), 
						Integer.parseInt(resultSet.getString("WrongReport")),
						Integer.parseInt(resultSet.getString("ReportRate")),
						Integer.parseInt(resultSet.getString("isActive")));

			}
			resultSet.close();
			statement.close();

		}

		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return user;
	}

	public User getUser(String u_name, String pass) throws SQLException{

		User user = new User();
		user.setUser_id(-1);

		try{

			String query = "SELECT * FROM Users WHERE Username='" + u_name
					+ "' AND password='" + pass+"'";
			statement = connect.createStatement();
			resultSet = statement.executeQuery(query);
			Date current = new Date();
			while(resultSet.next()){ 
				user.setAll(Integer.parseInt(resultSet.getString("UserID")), 
						resultSet.getString("Name"),
						resultSet.getString("Surname"),
						resultSet.getString("Username"),
						current, Integer.parseInt(resultSet.getString("isAdmin")),
						resultSet.getString("email"), resultSet.getString("password"),
						Integer.parseInt(resultSet.getString("Score")), 
						Integer.parseInt(resultSet.getString("WrongReport")),
						Integer.parseInt(resultSet.getString("ReportRate")),
						Integer.parseInt(resultSet.getString("isActive")));

			}

			statement.close();
			resultSet.close();

		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}
		return user;
	}

	public int getUserID(String user, String pass) throws SQLException{

		PreparedStatement statement = null;
		int userID = -1;
		String sql = "SELECT * FROM Users WHERE Username = ? AND password = ?";
		statement = connect.prepareStatement(sql);
		statement.setString(1,user);
		statement.setString(2, pass);
		resultSet = statement.executeQuery();
		if(resultSet.next()){
			userID =  resultSet.getInt(8);
		}

		statement.close();
		resultSet.close();	  
		return userID;
	}

	public boolean isInDb(String Username) throws SQLException{
		boolean inDb = false;
		PreparedStatement statement = null;
		String sql = "SELECT * FROM Users WHERE Username = ?";
		statement = connect.prepareStatement(sql);
		statement.setString(1, Username);
		resultSet = statement.executeQuery();
		if(resultSet.next()) {
			inDb = true;
		}

		statement.close();
		resultSet.close();
		return inDb;
	}

	public String getMail(String Username) throws SQLException{
		String email="";
		PreparedStatement statement = null;
		String sql = "SELECT * FROM Users WHERE Username = ?";
		statement = connect.prepareStatement(sql);
		statement.setString(1, Username);
		resultSet = statement.executeQuery();
		if(resultSet.next()) {
			email = resultSet.getString(6);
		}

		statement.close();
		resultSet.close();
		return email;
	}

	public String getPassword(String Username) throws SQLException{
		String password="";
		PreparedStatement statement = null;
		String sql = "SELECT * FROM Users WHERE Username = ?";
		statement = connect.prepareStatement(sql);
		statement.setString(1, Username);
		resultSet = statement.executeQuery();
		if(resultSet.next()) {
			password = resultSet.getString(7);
		}

		statement.close();
		resultSet.close();
		return password;
	}

	public boolean getActiveInfo(long UserId) throws SQLException{
		int isActive=0;
		PreparedStatement statement = null;
		String sql = "SELECT IsActive FROM Users WHERE UserID = ?";
		statement = connect.prepareStatement(sql);
		statement.setLong(1, UserId);
		resultSet = statement.executeQuery();
		if(resultSet.next()) {
			isActive = resultSet.getInt("IsActive");
		}

		statement.close();
		resultSet.close();
		if (isActive==1)
			return true;
		else
			return false;
	}

	public void banUser(int userID) throws SQLException{
		statement = connect.createStatement();
		String query = "UPDATE Users SET IsActive = 0 WHERE UserID = ?";
		PreparedStatement statement = connect.prepareStatement(query);
		statement.setInt(1,userID);
		statement.executeUpdate();
	}

	public void updateUser(int userID, int score, int wrongReport, int reportRate) throws SQLException{
		statement = connect.createStatement();
		String query = "UPDATE Users SET Score = ? , WrongReport = ? , ReportRate = ? WHERE UserID = ?";
		PreparedStatement statement = connect.prepareStatement(query);
		statement.setInt(1, score);
		statement.setInt(2,wrongReport);
		statement.setInt(3,reportRate);
		statement.setInt(4,userID);
		statement.executeUpdate();
	}

	public void addUser(User user) throws SQLException{
		statement = connect.createStatement();
		statement.executeUpdate("INSERT INTO Users(Name,Surname,Username,IsAdmin,email,password) " + "VALUES ('"+user.getName()+"','"+user.getSurname()+"','"+user.getUsername()+"',"+ user.getIsAdmin() +",'"+user.getEmail()+"','"+user.getPassword()+"')");

	}
	public void close() throws SQLException{
		if(resultSet!=null)
			resultSet.close();
		if(statement!=null)
			statement.close();
		if(connect!=null)
			connect.close();
	}

}
